package Dao.Grade.Imp;

import Dao.Grade.GradingDao;
import Entity.Grade.Grading;
import Util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class GradingDaoImp implements GradingDao {
    private PreparedStatement ps = null;
    private ResultSet rs = null;

    @Override
    public List<Grading> getGradingByTNO(String queryKey, Integer offset, Integer limit, String NO, Integer type, Connection conn) {
        List<Grading> gradings = new ArrayList<>();
        try {
            String sql;
            if (type == 1) {
                sql = "select " +
                        "id, sno, tno, is_original, remark, grade1, grade2, grade3, grade_state, pass_state " +
                        "from grading " +
                        "where sno = ? limit ?,?";
            } else {
                sql = "select " +
                        "id, sno, tno, is_original, remark, grade1, grade2, grade3, grade_state, pass_state " +
                        "from grading " +
                        "where tno = ? limit ?,?";
            }
            ps = conn.prepareStatement(sql);
            ps.setString(1, NO);
            ps.setInt(2, offset);
            ps.setInt(3, limit);
            rs = ps.executeQuery();
            while (rs.next()) {
                Integer id = rs.getInt(1);
                String sno = rs.getString(2);
                String tno = rs.getString(3);
                String isOriginal = rs.getString(4);
                String remark = rs.getString(5);
                Integer grade1 = rs.getInt(6);
                Integer grade2 = rs.getInt(7);
                Integer grade3 = rs.getInt(8);
                String gradeState = rs.getString(9);
                String passState = rs.getString(10);
                Grading grading = new Grading(id, sno, tno, isOriginal, remark, grade1, grade2, grade3, gradeState, passState, null);
                gradings.add(grading);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return gradings;
    }

    @Override
    public Integer updateGrading(Grading grading, Connection conn) {
        int result = 0;
        try {
            String sql = " update grading " +
                    "set is_original = ?, remark = ?, grade1 = ?, grade2 = ?, grade3 = ?, grade_state = ? " +
                    " where  sno = ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, grading.getIsOriginal());
            ps.setString(2, grading.getRemark());
            ps.setInt(3, grading.getGrade1());
            ps.setInt(4, grading.getGrade2());
            ps.setInt(5, grading.getGrade3());
            ps.setString(6, grading.getGradeState());
            ps.setString(7, grading.getSno());
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return result;
    }

    @Override
    public Integer insertSNO(String SNO, Connection conn) {
        int result = 0;
        try {
            String sql = "  insert into grading(sno) values (?) ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, SNO);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return result;
    }
}
